﻿using System;
using System.Collections.Generic;
using System.Text;
using System.Data;
using System.Data.SqlClient;

namespace DataAccess
{
    public class NewsDAO
    {
        ConnectionDatabase conn;
        SqlCommand cmd;
        SqlConnection con;
        public NewsDAO()
        {
            conn = new ConnectionDatabase();
        }
        // hien thi danh muc tin tuc 
        public DataTable ShowNews()
        {

            DataTable tbl = conn.ExecuteQueryDataTable("SP_ShowNews");
            return tbl;
        }
        // hien thi chi tiet tin tuc 
        public DataTable ShowNewsDetails(int codenews)
        {
            con = conn.GetConnection();
            cmd = new SqlCommand("SP_ShowNewsDetails", con);
            cmd.CommandType = CommandType.StoredProcedure;
            cmd.Parameters.Add("@CodeNews", SqlDbType.Int).Value = codenews;
            SqlDataAdapter da = new SqlDataAdapter(cmd);
            DataTable tbl = new DataTable();
            da.Fill(tbl);
            return tbl;
        }
        // code news 
        public int CodeNews(string title)
        {

            int code;

            con = conn.GetConnection();
            try
            {
                con.Open();
                cmd = new SqlCommand("SP_Codenews", con);
                cmd.CommandType = CommandType.StoredProcedure;
                cmd.Parameters.Add("@Title", SqlDbType.VarChar).Value = title;
                SqlDataReader dr = cmd.ExecuteReader();
                if (dr.Read())
                {
                    code = dr.GetInt32(0);
                    return code;
                }
                else
                {
                    code = 0;
                    return code;
                }
            }
            catch (Exception e) { throw; }
            finally { con.Close(); }


        }
        // thêm tin tức mới 
        public void InertNews(string title, string contentSummary, string content, string imageNews)
        {
            
            con = conn.GetConnection();
            try
            {
                con.Open();
                cmd = new SqlCommand("SP_InsertNews", con);
                cmd.CommandType = CommandType.StoredProcedure;
                cmd.Parameters.Add("@Title", SqlDbType.VarChar).Value = title;
                cmd.Parameters.Add("@ContentSummary", SqlDbType.Text).Value = contentSummary;
                cmd.Parameters.Add("@Content", SqlDbType.Text).Value = content;
                cmd.Parameters.Add("@ImageNews", SqlDbType.NVarChar).Value = imageNews;
                cmd.ExecuteNonQuery();
            }
            catch (Exception e) { throw; }
            finally { con.Close(); }

        }
        // hiển thị tin tức bên trang admin
        public DataTable NewsAdmin()
        {
            DataTable tbl;
            con = conn.GetConnection();
            try
            {
                con.Open();
                cmd = new SqlCommand("SP_ViewTitleDate", con);
                SqlDataAdapter da = new SqlDataAdapter(cmd);
                tbl = new DataTable();
                da.Fill(tbl);
                return tbl;
            }
            catch (Exception e) { throw; }
            finally { con.Close(); }
            
        }
        // xóa tin tức
        public void DeleteNews(int CodeNews)
        {
            con = conn.GetConnection();
            try
            {
                con.Open();
                cmd = new SqlCommand("SP_DeleteNews", con);
                cmd.CommandType = CommandType.StoredProcedure;
                cmd.Parameters.Add("@CodeNews", SqlDbType.Int).Value =CodeNews ;
                cmd.ExecuteNonQuery();
            }
            catch (Exception e) { throw; }
            finally { con.Close(); }
        }
        public DataTable ShowTopNews()
        {
            DataTable tbl = conn.ExecuteQueryDataTable("SP_Top2News");
            return tbl;
        }
        public DataTable ShowTopNewsTitlte()
        {
            DataTable tbl = conn.ExecuteQueryDataTable("SP_Top2NewsTitle");
            return tbl;
        }
        // những sự kiện
        public DataTable ShowTopEvent()
        {
            DataTable tbl = conn.ExecuteQueryDataTable("SP_ShowtopEvent");
            return tbl;
        }
        public DataTable ShowEvent()
        {
            DataTable tbl = conn.ExecuteQueryDataTable("select * from Event ORDER BY DateUp DESC");
            return tbl;
        }
        // thêm sự kiện mới 
        public void InertEvent( string Title,string ContentSummary,string Content,string ImageEvent,string StartTime,string TimeEnd)
        {

            con = conn.GetConnection();
            try
            {
                con.Open();
                cmd = new SqlCommand("SP_InsertEvent", con);
                cmd.CommandType = CommandType.StoredProcedure;
                cmd.Parameters.Add("@Title", SqlDbType.VarChar).Value = Title;
                cmd.Parameters.Add("@ContentSummary", SqlDbType.Text).Value = ContentSummary;
                cmd.Parameters.Add("@Content", SqlDbType.Text).Value = Content;
                cmd.Parameters.Add("@ImageEvent", SqlDbType.NVarChar).Value = ImageEvent;
                cmd.Parameters.Add("@StartTime", SqlDbType.NVarChar).Value = StartTime;
                cmd.Parameters.Add("@TimeEnd", SqlDbType.NVarChar).Value = TimeEnd;
                cmd.ExecuteNonQuery();
            }
            catch (Exception e) { throw; }
            finally { con.Close(); }
        }
        public DataTable ShowEventDetails(int codeEvent)
        {
            con = conn.GetConnection();
            cmd = new SqlCommand("SP_ShowEvenDetails", con);
            cmd.CommandType = CommandType.StoredProcedure;
            cmd.Parameters.Add("@CodeEvent", SqlDbType.Int).Value = codeEvent;
            SqlDataAdapter da = new SqlDataAdapter(cmd);
            DataTable tbl = new DataTable();
            da.Fill(tbl);
            return tbl;
        }
        public void DeleteEvent(int CodeEvent)
        {
            con = conn.GetConnection();
            try
            {
                con.Open();
                cmd = new SqlCommand("SP_DeleteEvent", con);
                cmd.CommandType = CommandType.StoredProcedure;
                cmd.Parameters.Add("@CodeEvent", SqlDbType.Int).Value = CodeEvent;
                cmd.ExecuteNonQuery();
            }
            catch (Exception e) { throw; }
            finally { con.Close(); }
        }
    }
}
